Scheduling Examples On each example worksheet, read the comments at the bottom of the sheet, then click Tools Solver... to examine the decision variables, constraints, and objective. To find the optimal solution, click the Solve button. In this series of models we will see how the Solver can help in staff scheduling with employee preferences, assigning people to offices, and in larger-scale problems such as airline crew scheduling and employee hiring, firing and training. In the Crew worksheet, we look at a very simplified application where a small airline needs to schedule crews to operate various flights. A crew 'rotation' must begin and end in the same city. In the Offices worksheet, we look at the common problem of assigning employees to offices, taking into account employee preferences. In the three Scheduling worksheets, an amusement park needs to assign employees to different work schedules to meet the demand for operation of the various rides on busy days. In Sched2, we take into account employee preferences in scheduling. In Sched3, we also take into account seniority and assign greater weight to senior employees' preferences. In the HireFire worksheet, we consider a company that has to change the composition of its workforce towards more highly trained employees. It can hire, fire and train employees at various costs. Different objectives are possible, such as minimizing costs or employee turnover. In the Troops worksheet, an army needs to move troops from 3 different training camps to 4 bases. The army can minimize cost or time by using this transportation model. Similar models occur in the Logistics Examples workbook.
278d21e9-d489-4675-a19b-0da8917e68d9.xls
Crew Scheduling
A small airline company maintains 2 daily flights between Salt Lake City, Chicago and Dallas. How should the company schedule the crews to minimize cost? Flight Schedule From Salt Lake City Salt Lake City Dallas Dallas Chicago Chicago To Dallas Chicago Salt Lake City Chicago Salt Lake City Dallas Departure 9:00 AM 10:00 AM 8:00 AM 9:00 AM 8:00 AM 10:00 AM Arrival 12:00 PM 2:00 PM 11:00 AM 11:00 AM 12:00 PM 12:00 PM Departure 2:00 PM 3:00 PM 2:00 PM 3:00 PM 2:00 PM 4:00 PM Arrival 5:00 PM 7:00 PM 5:00 PM 5:00 PM 6:00 PM 6:00 PM
A crew must leave and arrive in the same city. It is possible to fly the crew back aboard another airline. This would always be on a 8:00 PM flight. There are 6 airplanes in use. When a crew is actually flying a plane, the entire crew is paid $200 per hour. The other time spent (waiting between flights or flying aboard another airplane) costs the company $75 per hour. Possible Crew Rotations (S=Salt Lake City, D=Dallas, C=Chicago, ( )=Back with other company) Flying Hours SD+DS SD+(DS) SD+DC+(CS) SC+(CS) SC+CD+(DS) DS+SD DS+(SD) DS+SC+(CD) DC+CS+(SD) DC+CD CS+SD+(DC) CS+SC CD+DC CD+DS+(SC) Other Hours Cost Decision
6 3 5 4 6 6 3 7 6 4 7 8 4 7
2 11 10 10 5 3 12 7 5 5 7 3 3 9
Total Cost
$1,350 $1,425 $1,750 $1,550 $1,575 $1,425 $1,500 $1,925 $1,575 $1,175 $1,925 $1,825 $1,025 $2,075 $0
0 0 0 0 0 0 0 0 0 0 0 0 0 0
Twelve Flight Constraints Flight SD 1 SD 2 SC 1 SC 2 DS 1 DS 2 DC 1 DC 2 CS 1 CS 2 CD 1 CD 2 Number of crews
0 0 0 0 0 0 0 0 0 0 0 0
Page 2
278d21e9-d489-4675-a19b-0da8917e68d9.xls
Problem An airline company maintains a schedule of two daily flights between Salt Lake City, Dallas and Chicago. A crew that leaves a city in the morning has to return there at night. The crew can be brought back on another airline. There are 6 airplanes in use. When a crew is flying, the cost is $200 per hour. When a crew is waiting or being flown back, the cost is $75. How should the company schedule its crews to minimize cost? Solution 1) The airline has already determined what all the possible crew rotations can be. The variables are the binary integer decisions to accept rotations. In worksheet Crew these are defined as Rotation_decisions. 2) The constraints are simple. We want only one crew per flight. This gives Crews_on_flight = 1 and the logical constraint gives Rotation_decisions = binary 3) The objective is to minimize total cost. On worksheet Crew this cell is given the name Total_cost. Remarks Please confirm for yourself that the crew rotations chosen meet the required schedule. More sphisticated versions of this model are widely used in the airline industry, but the same approach can be used in scheduling truck drivers, boat crews, etc.
Page 3
278d21e9-d489-4675-a19b-0da8917e68d9.xls
Office Assignment
A company wants to assign 14 employees to 10 offices. Each employee has a preference for a certain office. How should the company assign employees to the offices to maximize the preference of all employees? Preferences (1=first, 10=last choice) Office 10 Office 1 Office 2 Office 3 Office 4 Office 5 Office 6 Office 7 Office 8 Office 9
Employee 1 Employee 2 Employee 3 Employee 4 Employee 5 Employee 6 Employee 7 Employee 8 Employee 9 Employee 10 Employee 11 Employee 12 Employee 13 Employee 14
3 2 1 5 3 2 10 8 1 7 3 2 1 3 6 4 9 1 2 1 10 6 5 1 8 9 10 9 10 3 7 3 5 6 5 1 6 8 10 6 3 5
4 6 9 9 8 5 9 3 5 2 2 9 9 9
6 1 7 5 5 6 5 2 4 5 9 10 1 1
5 7 4 4 2 8 3 4 3 4 8 2 2 2
8 9 10 7 9 8 4 10 3 6 2 5 8 6 1 10 9 10 7 4 2 7 10 3 6 8 4 7 7 8 9 10 2 1 6 7 1 7 8 6 1 10 4 6 3 4 7 8 3 4 5 7 10 4 8 7
Preference Office 10
Office 1
Office 2
Office 3
Office 4
Office 5
Office 6
Office 7
Office 8
Office 9
Assignments Employee 1 Employee 2 Employee 3 Employee 4 Employee 5 Employee 6 Employee 7 Employee 8 Employee 9 Employee 10 Employee 11 Employee 12 Employee 13 Employee 14 Total Required
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1
Total
0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Problem A company wants to assign 14 employees to 10 offices. There are four offices that require 2 people. Each employee has given their preference. A 1 means first choice, 2 means second choice, etc. How should the company assign the people to the offices to optimize the preferences of the employees? Solution
Page 4
278d21e9-d489-4675-a19b-0da8917e68d9.xls
1) The variables are the assignments of the people to different offices. On worksheet Offices these are given the name Assignments. 2) There are the following logical constraints Assignments = binary and the other constraints Assignments_per_employee = 1 Total_employees = Required_employees 3) The objective is to optimize the preference of the employees. That means we have to minimize the sum of the total preferences given to the assigned offices, defined on the worksheet as Total_preference. Remarks When everybody wants a different office, there will be no problems. If all employees prefer the same office (more likely!), the problem gets more difficult and it might be necessary to give an employee 7th or 8th choice. It might be wise, in that case, to add a constraint to say that no assignment worse than 5th choice is given, for instance. This may cause the problem to be infeasible, i.e., there is no possible solution. If this happens, you will have to relax the constraint on the assignments, e.g. no worse than 6th or even 7th choice.
Page 5
278d21e9-d489-4675-a19b-0da8917e68d9.xls
Personnel scheduling for an Amusement Park 1
For employees working five consecutive days with two days off, find the schedule which meets demand from attendance levels while minimizing payroll costs. Sch. A B C D E F G Days off Sunday, Monday Monday, Tuesday Tuesday, Wed. Wed., Thursday Thursday, Friday Friday, Saturday Saturday, Sunday Schedule Totals: Total Demand: Pay/Employee/Day: Payroll/Week: $40 $5,000 Employees 3 5 6 4 6 1 0 25 Sun Mon Tue Wed Thu 0 0 1 1 1 1 0 0 1 1 1 1 0 0 1 1 1 1 0 0 1 1 1 1 0 1 1 1 1 1 0 1 1 1 1 22 22 17 17 14 13 15 14 15 15 Fri 1 1 1 1 0 0 1 18 18 Sat 1 1 1 1 1 0 0 24 24
Problem An amusement park needs a certain number of employees each day of the week. Every employee must be on a schedule that gives him/her two consecutive days off. How many employees should the park hire and what schedule should they be on to minimize total payroll cost? Solution 1) The variables are the number of people hired for each of the 7 possible schedules. On worksheet Sched1 these are given the name Employees_per_schedule. 2) The logical constraints are Employees_per_schedule >= 0 via the Assume Non-Negative option Employees_per_schedule = integer There is also the constraint to have enough employees to operate the rides each day: Employees_per_day >= Required_per_day 3) The objective is to minimize payroll. This is defined on the worksheet as Payroll. Remarks This is an example of a simple, but classic personnel scheduling problem. Hospitals, schools, police forces, etc., can all use a model like this to optimize their personnel scheduling.
Page 6
278d21e9-d489-4675-a19b-0da8917e68d9.xls
Personnel scheduling for an Amusement Park 2
Maximize the preference of the employees' schedules while maintaining the schedule that minimizes payroll costs. Sch. A B C D E F G Days off Sunday, Monday Monday, Tuesday Tuesday, Wed. Wed., Thursday Thursday, Friday Friday, Saturday Saturday, Sunday Schedule Totals: Total Demand: Pay/Employee/Day: Payroll/Week: $40 $5,000 Schedule assigned to A B C D 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 3 0 5 0 7 0 3 Employees 3 5 7 3 7 0 0 25 Sun Mon Tue Wed Thu 0 0 1 1 1 1 0 0 1 1 1 1 0 0 1 1 1 1 0 0 1 1 1 1 0 1 1 1 1 1 0 1 1 1 1 22 22 17 17 13 13 15 14 15 15 Fri 1 1 1 1 0 0 1 18 18 Sat 1 1 1 1 1 0 0 25 24
Preference of employees (7=first ,1=last choice) A B C D E Employee 1 4 6 5 3 2 Employee 2 3 4 5 2 1 Employee 3 6 5 2 7 4 Employee 4 4 5 3 6 2 Employee 5 5 4 2 6 7 Employee 6 1 2 3 4 5 Employee 7 7 5 3 1 6 Employee 8 4 3 2 5 6 Employee 9 5 4 3 2 7 Employee 10 1 3 2 5 6 Employee 11 6 7 2 1 3 Employee 12 5 4 6 7 3 Employee 13 1 2 3 4 5 Employee 14 4 6 7 3 2 Employee 15 4 5 7 6 3 Employee 16 4 3 2 5 6 Employee 17 6 5 7 3 2 Employee 18 7 6 5 4 3 Employee 19 6 5 3 4 2 Employee 20 4 5 3 6 7 Employee 21 7 6 5 3 4 Employee 22 7 6 2 5 3 Employee 23 6 5 7 3 4 Employee 24 5 6 4 3 1 Employee 25 6 4 7 5 3 Totals
F 7 7 3 7 3 6 4 1 6 7 4 2 6 5 2 7 1 2 7 2 1 4 2 2 2
G 1 6 1 1 1 7 2 7 1 4 5 1 7 1 1 1 4 1 1 1 2 1 1 7 1
E 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 7
F 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Demand
Problem The amusement park discussed in model Sched1 has hired the necessary employees. It now needs to decide which employee goes on which schedule. Each employee has given a list with his/her preferences. A 7 means the most desired schedule, a 1means the least desired. How should the park divide the schedules among the employees? Solution 1) The variables are the schedules to which each employee is assigned. On worksheet Sched2 these are given
Page 7
278d21e9-d489-4675-a19b-0da8917e68d9.xls
the name Schedules. 2) The logical constraints are Schedules = binary The other constraints are Schedule_per_employee =1 Employees_scheduled = Employees_required 3) The objective is to optimize employee preference, and in this case that means maximize the sum of the preferences. This sum is defined on the worksheet as Total_preference. Remarks In this model we solved the problem after finding the optimal solution of Sched1. It is possible to find both solutions automatically. You can write a VBA macro to do this.
Page 8
278d21e9-d489-4675-a19b-0da8917e68d9.xls
G 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Total 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Preference 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Page 9
278d21e9-d489-4675-a19b-0da8917e68d9.xls
Personnel scheduling for an Amusement Park 3
Maximize the preference of the employees' schedules, taking into account seniority, while maintaining the schedule that minimizes payroll costs. Sch. A B C D E F G Days off Sunday, Monday Monday, Tuesday Tuesday, Wed. Wed., Thursday Thursday, Friday Friday, Saturday Saturday, Sunday Schedule Totals: Total Demand: Pay/Employee/Day: Payroll/Week: $40 $5,000 Schedule assigned to A B C D 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 3 0 5 0 7 0 3 Employees 3 5 7 3 7 0 0 25 Sun Mon Tue Wed Thu 0 0 1 1 1 1 0 0 1 1 1 1 0 0 1 1 1 1 0 0 1 1 1 1 0 1 1 1 1 1 0 1 1 1 1 22 22 17 17 13 13 15 14 15 15 Fri 1 1 1 1 0 0 1 18 18 Sat 1 1 1 1 1 0 0 25 24
Employee 1 Employee 2 Employee 3 Employee 4 Employee 5 Employee 6 Employee 7 Employee 8 Employee 9 Employee 10 Employee 11 Employee 12 Employee 13 Employee 14 Employee 15 Employee 16 Employee 17 Employee 18 Employee 19 Employee 20 Employee 21 Employee 22 Employee 23 Employee 24 Employee 25
Preference of employees Seniority (Years) A B C 5 5 3 2 6 3 4 5 4 4 3 2 7 3 4 2 3 1 3 4 2 4 5 3 7 3 4 2 5 2 4 3 3 4 3 2 2 3 4 2 5 4 2 5 7 5 3 2 5 1 2 3 4 4 2 5 9 5 4 3 5 3 5 1 6 4 3 5 7 3 4 2 6 5 4 3 4 3 5 4 3 2 5 1 4 2 5 1 6 5 2 4 6 3 1 5 7 1 4 3
D 4 2 1 1 2 1 5 1 5 5 1 4 4 3 1 4 2 5 2 2 3 3 3 2 5
E 1 1 5 5 5 2 1 5 1 1 3 1 5 1 2 2 1 1 1 1 4 4 1 4 2 Totals Demand
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 7
E Total 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Preference 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Problem We now extend the model as seen in Sched2 by adding another factor. When deciding which employee goes on which schedule, the amusement park decides to let the seniority of the employees affect the decision. The seniority is simply measured in years of employment at the park. How should the company assign the schedules to the employees? Solution
Page 10
278d21e9-d489-4675-a19b-0da8917e68d9.xls
The solution is almost identical to the one in Sched2. The difference is that instead of just adding the preferences, we now weight them by multiplying each preference by the employee's seniority. Remarks There are many ways of adjusting for seniority and other factors. By increasing and decreasing the size of the seniority factors, you can adjust the importance of the seniority to the desired level.
Page 11
278d21e9-d489-4675-a19b-0da8917e68d9.xls
Preference
Page 12
278d21e9-d489-4675-a19b-0da8917e68d9.xls
Company Reorganization
A company wants to reorganize its labour force. It currently has 3 different kind of employees; untrained, moderately trained and highly trained. Over the next 3 years, the company expects a necessary shift to more trained employees. How should the company reorganize to minimize cost? Or minimize the number of employees that have to be laid off? Employee information Available to Cost of lay- be hired per off year Highly Trained Moderately Trained Untrained Cost of hiring
Retraining of employees Untrained -> Moderately trained Moderately trained-> Highly trained
Cost
$700 $500 $350
Current
500 800 1200
Year 1
$250
$150
$400 $500
$100
Year 2 Year 3
Estimated number of employees that are required. Highly Trained Moderately Trained Untrained
800 1500 2000
1200 1500 1600
1500 2000 1000
2000 2500 0
Number of employees that are trained, hired or laid off. Number of employees trained Year 1 Untrained -> Moderately trained Moderately trained-> Highly trained Year 2 Year 3 Cost
0 0
Year 1
0 0
Year 2
0 0
Year 3 Cost
$0 $0
Number of employees hired Highly Trained Moderately Trained Untrained
0 0 0
Year 1
0 0 0
Year 2
0 0 0
Year 3 Cost
$0 $0 $0
Number of employees laid off Highly Trained Moderately Trained Untrained
0 0 0
0 0 0
0 0 0 0
$0 $0 $0
Total number of employees laid off Number of employees working Year 1 Highly Trained Moderately Trained Untrained Year 2
Year 3
800 1500 2000
800 1500 2000
800 1500 2000 $0
Total cost of reorganizing
Problem A company has three different kinds of employees. These are highly trained, moderately trained and untrained workers. The company expects a shift towards more highly trained employees necessary over the next few years. It is possible to train people at a certain cost Laying people off also costs a certain amount. How should the company reorganize to save costs and/or have as few lay-offs as possible? Solution 1) The variables are the number of people that are trained, hired and laid off. On worksheet HireFire these are
Page 13
278d21e9-d489-4675-a19b-0da8917e68d9.xls
given the names Trainees, Employees_hired, and Employees_laid_off. 2) The constraints can be divided into 2 parts. First, there are the logical constraints, all of which are defined via the Assume Non-Negative option: Trainees >= 0 Employees_hired >= 0 Employees_laid_off >= 0 Second, we have the training, laying off and hiring constraints. These do not use defined names, but are represented on the worksheet by the following cells: C22 <= B17 C23 <= B16 C26 : C28 <= C9 : C11 C31 : C33 <= B15 : B17 C40 : E42 = C15 : E17 D22 <= C41 D23 <= C42 D26 : D28 <= C9 : C11 D31 : D33 <= C40 : C42 E22 <= D41 E23 <= D42 E26 : E28 <= C9 : C11 E31 : E33 <= D40 : D42 In general, these constraints reflect the movement of employees from being hired untrained to becoming moderately trained or highly trained. 3) The main objective is to minimize cost. This is defined on the worksheet as Total_cost. Remarks The model as presented here will find the method of organization that has the lowest cost. It can involve large lay-offs. It is even possible that there are alternate solutions that require fewer lay-offs! To check this, you can add the constraint Total_cost = Solution, where Solution is the amount previously found by the solver. Then change the objective to minimize lay-offs. This way you are sure to find the solution that is least expensive and involves the fewest layoffs. If the number of lay-offs is still unacceptable, you could solve the original problem again and this time include a constraint like total_laid_off = 0, or * 1000. When this problem is solved you can use the sensitivity analysis report to see how much an extra lay off would cost.
Page 14
278d21e9-d489-4675-a19b-0da8917e68d9.xls
Troop Movement
An army wants to move troops from 3 training camps to 4 different bases. How should the troops be moved to minimize cost? Moving Cost Per Man Base 1 Camp 1 Camp 2 Camp 3 Base 2 Base 3 Base 4
$34 $42 $36
Base 1
$26 $33 $29
Base 2
$29 $28 $32
Base 3
$31 $35 $38
Base 4 Total Available
Number Of Troops Moved Camp 1 Camp 2 Camp 3 Total Required Cost
100 100 100 300 200 $11,200
100 100 100 300 250 $8,800
100 100 100 300 350 $8,900
100 100 100 300 300 $10,400
400 400 400
500 400 400
$39,300
Problem An army wants to move troops from 3 training camps to 4 different bases. All costs of moving a soldier from any camp to any base are known. How should the army move the troops to minimize cost? Solution 1) The variables are the number of soldiers that are moved from each camp to each base. On worksheet Troops these are given the name Troops_moved. 2) The constraints are Troops_moved >= 0 via the Assume Non-Negative option Troops_per_camp <= Troops_available Troops_per_base = Troops_required 3) The objective is to minimize the total cost. This is defined on the worksheet as Total_cost. Remarks This model is a transportation model, like those shown in the Logistics Examples workbook. You might wonder why there is no constraint to assure that the numbers of troops moved are integers. It is a mathematical property of these types of problems that if the constants in the constraints are integers, the solution values for the variables are always integers. It is beyond the scope of these examples to further explore this.
Page 15